package com.jx.lcs.datareport.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import com.jx.lcs.datareport.entity.DataReport;
import com.tianyu.jty.common.persistence.HibernateDao;

@Repository
public class UserCityReportDao extends HibernateDao<DataReport, String> {

	/**
	 * 按城市统计B端用户
	 */
	public List<Map<String, Object>> countUserByCity() {

		List<Map<String, Object>> result = new ArrayList<>();

		String sql = " SELECT if(CITY='','未知',CITY) as CITY, COUNT(1) FROM tb_user_info GROUP BY CITY HAVING count(1)>0 ORDER BY COUNT(1) DESC ";
		SQLQuery sqlQuery = createSQLQuery(sql);

		@SuppressWarnings("unchecked")
		Iterator<Object[]> it = sqlQuery.list().iterator();
		while (it.hasNext()) {
			Object[] temp = it.next();
			Map<String, Object> element = new HashMap<>();
			element.put("city", temp[0]);
			element.put("total", temp[1]);
			result.add(element);
		}

		return result;
	}
}
